﻿using SqlPerfomance.V2.DomainModel.Enums;
using SqlPerfomance.V2.DomainModel.Models;
using SqlPerfomance.V2.DomainModel.NodeTree;
using SqlPerfomance.V2.DomainModel.NodeTree.Interfaces;
using SqlPerfomance.V2.Helpers;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SqlPerfomance.V2.DAL
{
	public class AdjancyListTreeRepository : RealDataRepository<AdjancyListTreeNode>
	{
        private DatabaseLogger logger;
        public AdjancyListTreeRepository()
        {
            logger = new DatabaseLogger();
        }

		#region CreateAdjancyListTree

		public override ITree<AdjancyListTreeNode> CreateTree(INode<CommonTreeNode> commonTree)
		{
			var alTree = NodeTree<AdjancyListTreeNode>.NewTree();
			//alTree.Inserted += AlTreeOnInserted;

			ITree<AdjancyListTreeNode> alTreeRoot = alTree;
			var alTopNode = new AdjancyListTreeNode()
			{
				Id = 1,
				Name = "root",
				Parent_Id = null
			};
			INode<AdjancyListTreeNode> alTop = alTreeRoot.AddChild(alTopNode);
			INode<CommonTreeNode> topNode = commonTree.Root.DirectChildren.Nodes.First();
			AddItem(alTop, topNode);

			//alTree.Inserted -= AlTreeOnInserted;
			return alTree;
		}

		private void AddItem(INode<AdjancyListTreeNode> alNode, INode<CommonTreeNode> commonTree)
		{
			foreach (var node in commonTree.DirectChildren.Nodes)
			{
				var child = new AdjancyListTreeNode()
				{
					Id = node.Data.Id,
					Name = node.Data.Name,
					Parent_Id = node.Data.Parent_Id
				};
				var alChild = alNode.AddChild(child);
				AddItem(alChild, node);
			}
		}

		private void AlTreeOnInserted(object sender, NodeTreeInsertEventArgs<AdjancyListTreeNode> nodeTreeInsertEventArgs)
		{
			NodeTreeSave(nodeTreeInsertEventArgs.Node, ConnectionStrings.SqlServerConnectionString);
			//NodeTreeSave(nodeTreeInsertEventArgs.Node, ConnectionStrings.MySqlConnectionString);
		}

		#endregion

		#region LoadAlTreeFromDb

		#region SqlQueries
		/// <summary>
		/// Запрос для получения дочерних узлов (возварщает сам узел и его дочерние узлы)
		///{0} - Parent Id элемента, {1} - таблица alTree
		/// </summary>
		private const string AlTreeQuery = @"
	SELECT id, parent_id, Name 
FROM {1} AS Parent
WHERE parent_id = {0}";

		/// <summary>
		/// {0} - Таблица alTree
		/// </summary>
		private const string AlTreeTopNodeQuery = @"
SELECT {1} node.id AS Id
, node.parent_id As Parent_id
, node.name AS Name
FROM
{0} AS node
WHERE node.parent_id IS NULL
{2}";

		private const string AlTreeTopNodeQueryById = @"
SELECT {1} node.id AS Id
, node.parent_id As Parent_id
, node.name AS Name
FROM
{0} AS node
WHERE node.parent_id = {2}
{3}";

		private const string AlTreeSelectQuery = @"SELECT Id, Name, parent_id FROM {0}";
		#endregion

		#region SqliteQueries

		private const string SqliteTableName = "AdjancyListTreeTable";
		private const string SqliteTableQuery =
			"create table {0}(id int primary key, parent_id int, Name text);";
		private const string SqliteInsertQuery =
			"insert into {0}(id, Parent_id, Name) values({1}, {2}, '{3}');";

		private const string SqliteIndexQuery = @"CREATE INDEX `indx_AdjancyListTreeTable_Id` ON `{0}` (`Id` ASC);
CREATE INDEX `indx_AdjancyListTreeTable_parent_id` ON `{0}` (`parent_id` ASC);";

		#endregion

		private ITree<AdjancyListTreeNode> AlTree { get; set; }

		public ITree<AdjancyListTreeNode> GetAdjancyListTree(ConnectionStrings conStr)
		{
			LoadAlTree(conStr, GetNodeTreeTableName(typeof(AdjancyListTreeNode)));
			var result = AlTree;
			AlTree = null;
			return result;
		}

		private void LoadAlTree(ConnectionStrings conStr, string tableName)
		{
			AlTree = NodeTree<AdjancyListTreeNode>.NewTree();
			var topNode = AlTree.AddChild(GetTopNodeOfAdjancyListTree(conStr, tableName));

			var allNodes = LoadAlTreeFromSqlDatabase(conStr, tableName);

			var connection = new SQLiteConnection("Data Source=:memory:;Version=3;New=True;");
			connection.Open();

			InsertAlTreeIntoSqliteDatabase(connection, allNodes);

			AddAlTreeChildNodes(connection, topNode);
			connection.Close();
		}

		public IEnumerable<AdjancyListTreeNode> LoadAlTreeFromSqlDatabase(ConnectionStrings conStr, string tableName)
		{
			var alTreeNodes = new List<AdjancyListTreeNode>();
			using (DbConnection conn = GetDbConnection(conStr))
			{
				conn.Open();
				using (DbCommand cmd = conn.CreateCommand())
				{
                    string cmdText = string.Format(AlTreeSelectQuery, tableName);
                    cmd.CommandText = cmdText;
					DbDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    logger.Log(conStr, cmdText, "AL_LoadTreeFromSqlDatabase");
					while (reader.Read())
					{
						var node = new AdjancyListTreeNode()
						{
							Id = Convert.ToInt32(reader["Id"]),
							Name = (string)reader["Name"],
							Parent_Id = ((int?)(reader.IsDBNull(2) ? 0 : (long?)reader["parent_id"]))
						};
						alTreeNodes.Add(node);
					}
				}
			}
			return alTreeNodes;
		}

		object Read(IDataReader reader, string name)
		{
			var val = reader[name];
			return val is DBNull ? (object)null : val;
		}

		private void InsertAlTreeIntoSqliteDatabase(SQLiteConnection connection, IEnumerable<AdjancyListTreeNode> allNodes)
		{
			using (var sqliteCreateTableCmd = new SQLiteCommand(connection))
			{
				sqliteCreateTableCmd.CommandText = string.Format(SqliteTableQuery, SqliteTableName);
				sqliteCreateTableCmd.ExecuteNonQuery();
			}

			using (var insertQuery = new SQLiteCommand(connection))
			{
				foreach (var node in allNodes)
				{
					insertQuery.CommandText = string.Format(
						SqliteInsertQuery,
						SqliteTableName,
						node.Id,
						(node.Parent_Id ?? (object)"null"),
						node.Name);
					insertQuery.ExecuteNonQuery();
				}
			}

			using (var command = new SQLiteCommand(connection))
			{
				command.CommandText = string.Format(SqliteIndexQuery, SqliteTableName);
				command.ExecuteNonQuery();
			}
		}

		private AdjancyListTreeNode GetTopNodeOfAdjancyListTree(ConnectionStrings conStr, string tableName)
		{
			string query = string.Empty;
			switch (conStr)
			{
				case ConnectionStrings.MySqlConnectionString:
					query = string.Format(AlTreeTopNodeQuery, tableName, string.Empty, "LIMIT 1");
					break;
				case ConnectionStrings.SqlServerConnectionString:
					query = string.Format(AlTreeTopNodeQuery, tableName, "TOP 1", string.Empty);
					break;
			}
			var alTree = new AdjancyListTreeNode();
			using (DbConnection conn = GetDbConnection(conStr))
			{
				conn.Open();
				using (DbCommand cmd = conn.CreateCommand())
				{
					cmd.CommandText = query;
					cmd.CommandTimeout = 360;
					DbDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
					while (reader.Read())
					{
						alTree.Id = Convert.ToInt32(reader["Id"]);
						alTree.Name = (string)reader["Name"];
						alTree.Parent_Id = (reader["Parent_id"]) as int?;
					}
				}
			}
			return alTree;
		}

		private AdjancyListTreeNode GetTopNodeOfAdjancyListTreeById(ConnectionStrings conStr, string tableName, int id)
		{
			string query = string.Empty;
			switch (conStr)
			{
				case ConnectionStrings.MySqlConnectionString:
					query = string.Format(AlTreeTopNodeQueryById, tableName, string.Empty, id, "LIMIT 1");
					break;
				case ConnectionStrings.SqlServerConnectionString:
					query = string.Format(AlTreeTopNodeQueryById, tableName, "TOP 1", id, string.Empty);
					break;
			}
			var alTree = new AdjancyListTreeNode();
			using (DbConnection conn = GetDbConnection(conStr))
			{
				conn.Open();
				using (DbCommand cmd = conn.CreateCommand())
				{
					cmd.CommandText = query;
					cmd.CommandTimeout = 360;
					DbDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
					while (reader.Read())
					{
						alTree.Id = Convert.ToInt32(reader["Id"]);
						alTree.Name = (string)reader["Name"];
						alTree.Parent_Id = (reader["Parent_id"]) as int?;
					}
				}
			}
			return alTree;
		}


		private void AddAlTreeChildNodes(SQLiteConnection conn,
			INode<AdjancyListTreeNode> tree)
		{
			var childrens = new List<AdjancyListTreeNode>();
			using (SQLiteCommand cmd = conn.CreateCommand())
			{
				cmd.CommandText = string.Format(AlTreeQuery, tree.Data.Id,
					SqliteTableName);
				var reader = cmd.ExecuteReader();
				while (reader.Read())
				{
					var node = new AdjancyListTreeNode
					{
						Id = (int)reader["Id"],
						Name = (string)reader["Name"],
						Parent_Id = reader["Parent_Id"] as int?
					};
					childrens.Add(node);
				}
			}
			if (childrens.Count != 0)
			{
				Parallel.ForEach(childrens, child =>
				{
					var addedChild = tree.AddChild(child);
					AddAlTreeChildNodes(conn, addedChild);
				});
			}
		}

		#endregion

		#region tests

		public int GetTreeHeight(ConnectionStrings connectionString, string tableName)
		{
			string query = @"SELECT COUNT(DISTINCT parent_id) AS Cnt
							FROM {0}
							WHERE parent_id IS NOT NULL;
							";
			using (DbConnection conn = GetDbConnection(connectionString))
			{
				conn.Open();
				using (DbCommand cmd = conn.CreateCommand())
				{
					cmd.CommandText = string.Format(query, tableName);
					DbDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
					int cnt = 0;
					while (reader.Read()) cnt = reader["Cnt"] != DBNull.Value ? reader.GetInt32(0) : 0;
					return cnt;
				}
			}
		}

		public void SelectPathById(ConnectionStrings connectionString, string tableName, long id, int treeHeight)
		{
			StringBuilder fields = new StringBuilder();
			StringBuilder joins = new StringBuilder();
			int i = 1;
			for (i = 1; i <= treeHeight; i++)
			{
				fields.AppendFormat("t{0}.id AS id1, t{0}.name AS name1, t{0}.parent_id AS parent_id{0}", i);
				fields.Append(i == treeHeight ? "\n" : ",\n");
				if (i > 1)
				{
					joins.AppendFormat("LEFT JOIN {0} AS t{1} ON t{1}.parent_id = t" + (i - 1) + ".id\n", tableName, i);
				}
			}
			string sql = string.Format("SELECT {0} FROM {1} AS t1 {2} WHERE t1.parent_id IS NULL AND t{3}.id = {4}", fields.ToString(), tableName, joins.ToString(), (i - 1), id);
            logger.Log(connectionString, sql, "AL_SelectPathById");
			using (DbConnection conn = GetDbConnection(connectionString))
			{
				conn.Open();
				using (DbCommand cmd = conn.CreateCommand())
				{
					cmd.CommandText = sql;
					cmd.ExecuteNonQuery();
				}
			}
		}

		public ITree<AdjancyListTreeNode> SelectBranchById(ConnectionStrings connectionString, string tableName, int id)
		{
			ITree<AdjancyListTreeNode> tree = NodeTree<AdjancyListTreeNode>.NewTree();
			var topNode = tree.AddChild(GetTopNodeOfAdjancyListTreeById(connectionString, tableName, id));
			SelectBranchByIdRecur(connectionString, topNode, tableName);
            string sqlForLog = string.Format(AlTreeQuery, topNode.Data.Id,tableName);
            logger.Log(connectionString, sqlForLog + "/*выборка рекурсивная*/", "AL_SelectBranchById");
			return tree;
		}

		private void SelectBranchByIdRecur(ConnectionStrings connectionString,
			INode<AdjancyListTreeNode> tree, string tableName)
		{
			var childrens = new List<AdjancyListTreeNode>();
			using (DbConnection conn = GetDbConnection(connectionString))
			{
				conn.Open();
				using (DbCommand cmd = conn.CreateCommand())
				{
					cmd.CommandText = string.Format(AlTreeQuery, tree.Data.Id,
						tableName);
					var reader = cmd.ExecuteReader();
					while (reader.Read())
					{
						var node = new AdjancyListTreeNode
						{
							Id = (int)reader["Id"],
							Name = (string)reader["Name"],
							Parent_Id = reader["Parent_Id"] as int?
						};
						childrens.Add(node);
					}
				}
			}
			if (childrens.Count != 0)
			{
				Parallel.ForEach(childrens, child =>
				{
					var addedChild = tree.AddChild(child);
					SelectBranchByIdRecur(connectionString, addedChild, tableName);
				});
			}
		}

		public AdjancyListTreeNode SelectParentById(ConnectionStrings connectionString, string tableName, long id)
		{
			using (var connection = GetDbConnection(connectionString))
			{
				connection.Open();
				using (var cmd = connection.CreateCommand())
				{
                    string sql = string.Format(@"SELECT parent.id
														,parent.parent_id
														,parent.name
															FROM {0} node, {0} parent
															WHERE node.parent_id = parent.id
															AND node.id = {1}", tableName, id);
					cmd.CommandText = sql;
					AdjancyListTreeNode alNode = new AdjancyListTreeNode();
					var reader = cmd.ExecuteReader();
                    logger.Log(connectionString, sql, "AL_SelectParentById");
					while (reader.Read())
					{
						alNode.Id = (int)reader["id"];
						alNode.Name = (string)reader["name"];
						long? parent = reader["parent_id"] == DBNull.Value ? (long?)null : (long)reader["parent_id"];
						alNode.Parent_Id = parent;
					}
					return alNode;
				}
			}
		}

		public IEnumerable<AdjancyListTreeNode> SelectChildrenById(ConnectionStrings connectionString, string tableName, int id)
		{
			using (var connection = GetDbConnection(connectionString))
			{
				connection.Open();
				using (var cmd = connection.CreateCommand())
				{
                    string sql = string.Format(@"SELECT a.id
														,a.name
														,a.parent_id 
													FROM AlTreeTable a
													WHERE a.parent_id = {0}", id);
                    cmd.CommandText = sql;
                    logger.Log(connectionString, sql, "AL_SelectChildrenById");
					var reader = cmd.ExecuteReader();
					var lst = new List<AdjancyListTreeNode>();
					while (reader.Read())
					{
						long? parent = reader["parent_id"] == null ? (long?)null : (long)reader["parent_id"];
						var node = new AdjancyListTreeNode
						{
							Id = (int)reader["id"],
							Name = (string)reader["name"],
							Parent_Id = parent
						};
						lst.Add(node);
					}
					return lst;
				}
			}
		}

		public void AddNodeToRootChildren(ConnectionStrings connectionString, string tableName, int parentId)
		{
			using (var conn = GetDbConnection(connectionString))
			{
				conn.Open();
				using (var cmd = conn.CreateCommand())
				{
                    string sql = string.Format(@"INSERT INTO {0} (parent_id, name) VALUES ({1}, '{2}');", tableName, parentId, RandomStrings.GetRandomString(15));
                    string cmdText = sql;
                    logger.Log(connectionString, sql, "AL_AddNode");
					cmd.CommandText = cmdText;
					cmd.ExecuteNonQuery();
				}
			}
		}

		public void MoveNode(ConnectionStrings connectionString, string tableName, int newParentId, long id)
		{
			using (var conn = GetDbConnection(connectionString))
			{
				conn.Open();
				using (var cmd = conn.CreateCommand())
				{
                    string sql = string.Format("UPDATE {0} SET parent_id = {1} WHERE id = {2};", tableName, newParentId, id);
                    cmd.CommandText = sql;
                    logger.Log(connectionString, sql, "AL_MoveNode");
					cmd.ExecuteNonQuery();
				}
			}
		}

		public void DeleteNode(ConnectionStrings connectionString, string tableName, long id)
		{
			using (var conn = GetDbConnection(connectionString))
			{
				conn.Open();
				using (var cmd = conn.CreateCommand())
				{
                    string sql = string.Format(@"DELETE FROM {0} WHERE id = {1}", tableName, id);
                    cmd.CommandText = sql;
                    logger.Log(connectionString, sql, "AL_DeleteNode");
					cmd.ExecuteNonQuery();
				}
			}
		}
		#endregion

		private IEnumerable<AdjancyListTreeNode> GetData(string sql, ConnectionStrings connectionString)
		{
			using (var connection = GetDbConnection(connectionString))
			{
				connection.Open();
				using (var cmd = connection.CreateCommand())
				{
					cmd.CommandText = sql;
					var reader = cmd.ExecuteReader();
					var nodes = new List<AdjancyListTreeNode>();
					while (reader.Read())
					{
						long? parent = reader["parent_id"] == DBNull.Value ? (long?)null : (long)reader["parent_id"];
						nodes.Add(new AdjancyListTreeNode()
							{
								Id = (int)reader["id"],
								Name = (string)reader["name"],
								Parent_Id = parent
							});
					}
					return nodes;
				}
			}
		}
	}
}
